/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import model.dto.Question;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import model.util.DBUtil;

/**
 *
 * @author PhucAn
 */
public class QuestionSetDAO {
    
   private final String sqlSelectByExam="select question.id,question.content,category_id,hard_level,mark,is_single_choice from question join question_set on question.id=question_set.question_id join exam on question_set.exam_id=exam.id where exam_id=?"; 
   private final String sqlCreate="insert into question_set(exam_id,question_id) values(?,?)";
   private final String sqlDelete="delete from question_set where exam_id=? and question_id=?";
   private final  String sqlDeleteByExam="delete from question_set where exam_id=?";
   Connection conn=null;
   Logger log=Logger.getLogger(QuestionSetDAO.class);
   
   public ArrayList<Question> getQuestions(int examId){
       ArrayList<Question> list=new ArrayList<Question>();
       try{
           conn=DBUtil.getConnection();
           if(conn==null)return list;
           PreparedStatement ps=conn.prepareStatement(sqlSelectByExam);
           ps.setInt(1, examId);
           ResultSet rs=ps.executeQuery();
           while(rs.next()){
               Question q=new Question();
               q.setId(rs.getInt("id"));
               q.getCategory().setId(rs.getInt("category_id"));
               q.setContent(rs.getString("content"));
               q.setLevel(rs.getByte("hard_level"));
               q.setMark(rs.getFloat("mark"));
               q.setSingleChoice(rs.getBoolean("is_single_choice"));
               list.add(q);
           }
        }catch(SQLException ex){
           log.info(ex.getMessage());
       }catch(Exception ex){
           log.info(ex.getMessage());
       }
       return list;
   }
   public int create(int examId, int questionId){
       int effectedRows=0;
       try{
           conn=DBUtil.getConnection();
           if(conn==null)return 0;
           PreparedStatement ps=conn.prepareStatement(sqlCreate);
           ps.setInt(1, examId);
           ps.setInt(2, questionId);
           effectedRows=ps.executeUpdate();
       }catch(SQLException ex){
           log.info(ex.getMessage());
       }catch(Exception ex){
           log.info(ex.getMessage());
       }
       return effectedRows;
   }
     public int delete(int examId, int questionId){
       int effectedRows=0;
       try{
           conn=DBUtil.getConnection();
           if(conn==null)return 0;
           PreparedStatement ps=conn.prepareStatement(sqlDelete);
           ps.setInt(1, examId);
           ps.setInt(2, questionId);
           effectedRows=ps.executeUpdate();
       }catch(SQLException ex){
           log.info(ex.getMessage());
       }catch(Exception ex){
           log.info(ex.getMessage());
       }
       return effectedRows;
   }
     /**
      * 
      * @param examId identifier of the exam
      * @param deleteQuestion also delete question in database or not. If 'true' then those question will be deleted
      * @return 
      */
     public int delete(int examId,boolean  deleteQuestion){
       int effectedRows=0;
       try{
           conn=DBUtil.getConnection();
           if(conn==null)return 0;
           PreparedStatement ps=conn.prepareStatement("delete from question where id in (select question_id from question_set where exam_id=?)");
           ps.setInt(1, examId);
           if(deleteQuestion){
               ps.executeUpdate();
           }
           ps.close();
           ps=conn.prepareStatement(sqlDeleteByExam);
           ps.setInt(1, examId);    
           effectedRows=ps.executeUpdate();
       }catch(SQLException ex){
           log.info(ex.getMessage());
       }catch(Exception ex){
           log.info(ex.getMessage());
       }
       return effectedRows;
   }
   
   
}
